package com.red.wood.impt;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import com.red.wood.base.BaseTest;
import com.red.wood.common.springjdbc.CommonDao;

//@Ignore
public class ImportTemplateDemo extends BaseTest{

	private static final Log log = LogFactory.getLog(ImportTemplateDemo.class);
	
	@Resource(name="jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private CommonDao commonDao;
	
	@Test
	public void importData(){
		try {
			Workbook workbook = null;
			String filename = "D:"+File.separator+"data"+File.separator+"interface"+File.separator+"重点地区网站视图.xlsx"; 
			String fileType = ".xlsx";
			if(StringUtils.equals(fileType, ".xlsx")){
				workbook = new XSSFWorkbook(new FileInputStream(filename));
			}else{
				workbook = new HSSFWorkbook(new FileInputStream(filename));
			}
			readExcel(workbook,fileType);
		}catch(Exception e) {
			if(log.isErrorEnabled()) {
				log.error(e.getMessage(),e);
			}
		}
	}

	public void readExcel(Workbook workbook,String fileType){
		StringBuffer validInfo = null;
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			Sheet sheet = workbook.getSheetAt(numSheet);
			//String tableName = sheet.getSheetName();//约定sheet页名称为表名称
			String tableName = getTableName(sheet.getSheetName());
			if (sheet == null || sheet.getRow(1) == null) {
				continue;
			}
			// 循环行Row,第一行为数据中字段，第二行为字段头，第三行开始为数据
			// 获取字段名
			Row rowOne = sheet.getRow(0);// 获取数据库中字段
			Row rowName = sheet.getRow(1);
			int columns = rowName.getPhysicalNumberOfCells();
			boolean validFlag = true;
			for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
				try {
					validInfo = new StringBuffer();
					Row row = sheet.getRow(rowNum);
					if (row == null) {
						continue;
					}
					// 循环列Cell 如： 0 策略名称 1 指定生效时间 ...
					Map<String, Object> param = new HashMap<String, Object>();
					for (int i = 0; i < columns; i++) {
						String name = getValue(rowOne.getCell(i));
						if (StringUtils.isBlank(name)) {
							//提示用户EXECL中字段有为空的
							validInfo.append(name+"不为空");
						}
						Cell cell = row.getCell(i);
						if(cell == null) {
							System.err.println("第"+(rowNum+1)+"行，"+name+"值为空！");
							param.put(name,"");
							continue;
						}
						//防止数字变为变成科学计数法，默认都设置为string，若有小数在特殊处理
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						String value = StringUtils.trim(getValue(cell));
						if(StringUtils.isBlank(value)) {
							System.err.println("第"+(rowNum+1)+"行，"+name+"值为空！");
						}
						param.put(name, value);
					}
					list.add(param);
					
					boolean flag  = valid(validInfo,workbook,columns,row,fileType);
					if(!flag) {
						validFlag = false;
					}
				}catch(Exception e) {
					if(log.isErrorEnabled()) {
						log.error(e.getMessage(), e);
					}
				}
			}
			//校验
			if(validFlag) {
				if(list != null && list.size() > 0) {
					String delSql = "delete from "+tableName;
					commonDao.deleteTableData(delSql);
					
					Map<String,Object> map = list.get(0);
					Set<String> keys = map.keySet();
					StringBuffer keyBuffer = new StringBuffer();
					StringBuffer valuesBuffer = new StringBuffer();
					for(String key : keys) {
						keyBuffer.append(key).append(",");
						valuesBuffer.append("?").append(",");
					}
					String column = keyBuffer.toString().substring(0,keyBuffer.length()-1);
					String values = valuesBuffer.toString().substring(0,valuesBuffer.length()-1);
					String sql = "insert into "+tableName+"("+column+") values("+values+")";
					System.out.println(sql);
					
					commonDao.insertBatchTableData(list, sql, column,5000);
				}
			}
		}
	}

	public static String getTableName(String key) {
		Map<String,String> map = new HashMap<String,String>();
		
		//重点问题情况监测
		map.put("疑似违规经营情况","t_important_problem_left1");
		map.put("信安系统运行异常情况","t_important_problem_left2");
		map.put("重点问题情况占比","t_important_problem_left3");
		map.put("重点问题省份分布","t_important_problem_map");
		map.put("重点问题企业TOP5","t_important_problem_right");
		
		//移动上网日志
		map.put("日志留存分布图","t_internet_log_map");
		map.put("上网方式占比","t_internet_log_left2");
		map.put("运营商日志留存准确率趋势","t_internet_log_right1");
		map.put("上网方式日志留存准确率趋势","t_internet_log_right2");
		
		//日常监测与应急保障
		map.put("标题汇总-监测与处置","t_daily_monitoring_count");
		map.put("重大项保障情况","t_daily_monitoring_left");
		map.put("监测信息分类、监测任务来源、处置信息分类、处置任务来源","t_daily_monitoring_right");
		map.put("行业管理、扫黄打非、教育考试、日常应急","t_daily_monitoring_buttom");
		
		//互联网舆情分析
		map.put("标题汇总-事件","t_network_analysis_count");
		map.put("事件名称列表","t_network_analysis_event");
		map.put("事件传播的省份","t_network_analysis_map");
		map.put("事件传播趋势","t_network_analysis_right1");
		map.put("事件传播类型占比","t_network_analysis_right2");
		
		//扫黄打非
		map.put("监测任务情况、监测域名加速情况","t_against_pornography_pie");
		map.put("定位监测、监测域名接入、处置内容分类、拦截处置数、监测情况","t_against_pornography_bar");
		
		//工业互联网总体态势
		map.put("标题汇总-总体态势","t_general_situation_count");
		map.put("重点省份工业互联网接入情况","t_general_situation_left1");
		map.put("接入形式","t_general_situation_left2");
		map.put("工业互联网设备-平台全国分布","t_general_situation_map");
		map.put("工业互联网在线设备情况","t_general_situation_right1");
		map.put("工业互联网平台业务情况", "t_general_situation_right2");
		
		//重点地区工业互联网情况
		map.put("海尔平台全国流量图","t_industry_areas_map");
		map.put("平台排名情况","t_industry_areas_left1");
		map.put("平台接入情况","t_industry_areas_left2");
		map.put("在线设备地市TOP5","t_industry_areas_left3");
		map.put("漏洞类型、漏洞等级、暴露设备类型","t_industry_areas_right1");
		map.put("平台、设备漏洞详情", "t_industry_areas_right2");
		
		//重点工业互联网平台情况
		map.put("标题汇总-平台","t_industry_platform_count");
		map.put("工业互联网平台接入情况-地图数据","t_industry_platform_map");
		map.put("工业互联网平台接入情况-表格数据","t_industry_platform_map_form");
		map.put("漏洞数量省份、漏洞平台数量","t_industry_platform_bar");
		map.put("平台提供商分布、漏洞类型、漏洞等级","t_industry_platform_pie");
		map.put("平台活跃度及接入终端数量","t_industry_platform_left3");
		
		//相关联网设备情况
		map.put("工业互联网设备接入情况-地图数据","t_industry_device_map_left1");
		map.put("工业互联网设备接入情况-表格数据","t_industry_device_map_form");
		map.put("在线设备协议、工业互联网在线设备厂商、漏洞类型","t_industry_device_bar");
		map.put("漏洞实时情况","t_industry_device_right1");
		map.put("工业互联网在线设备类型、漏洞风险分布","t_industry_device_pie");
		
		//基础企业安全责任考核
		map.put("安全责任考核-地图数据","t_safe_responsible_map");
		map.put("运营商考核对比","t_safe_responsible_right1");
		map.put("运营商扣分情况占比","t_safe_responsible_right2");
		
		//新技术新业务安全评估
		map.put("安全评估-地图数据","t_safe_assessment_map");
		map.put("类型总占比","t_safe_assessment_right1_tab1");
		map.put("企业占比","t_safe_assessment_right1_tab2");
		map.put("业务类型、风险类型","t_safe_assessment_right2");
		
		//增值电信企业管理
		map.put("标题汇总-增值电信企业","t_increment_telecom_count");
		map.put("增值电信企业分布情况","t_increment_telecom_map");
		map.put("各业务增值电信企业数","t_increment_telecom_right1");
		map.put("通过信安系统评测的企业数量变化","t_increment_telecom_right2");
		
		//------------接口数据-----------------------
		//全国接入资源视图
		map.put("全国接入资源-数据汇总","t_nation_acc_res_count");
		map.put("全国接入企业","t_nation_acc_res_left1");
		map.put("全国覆盖资源","t_nation_acc_res_left2");
		map.put("全国接入资源分布","t_nation_acc_res_map");
		map.put("对接企业数量","t_nation_acc_res_right1");
		map.put("企业牌照类型","t_nation_acc_res_right2");
		map.put("全国机房数","t_nation_room");
		
		//省内接入资源视图
		map.put("接入资源分布","t_province_acc_res_map");
		map.put("接入企业数变化趋势","t_province_acc_res_left2");
		map.put("各企业带宽排名","t_province_acc_res_right1_t1");
		map.put("服务区域分类","t_province_acc_res_right1_t2");
		map.put("云业务带宽排行","t_province_acc_res_right2");
		map.put("CDN企业资源排名","t_province_acc_res_right3");
		
		//全国IDC/ISP资源概览
		map.put("IDCISP数据汇总","t_nation_idc_res_left1");
		map.put("IDCISP地图数据","t_nation_idc_res_map");
		map.put("各省接入企业数量排名","t_nation_idc_res_right1");
		map.put("增值企业接入域名数量排名","t_nation_idc_res_right2");
		map.put("基础企业接入域名数量排名","t_nation_idc_res_right3");
		
		//全国云资源概览
		map.put("云资源数据汇总","t_nation_cloud_res_left1");
		map.put("云资源地图数据","t_nation_cloud_res_map");
		map.put("云企业用户数","t_nation_cloud_res_right_t1");
		map.put("云企业机房数","t_nation_cloud_res_right_t2");
		map.put("云企业IP数","t_nation_cloud_res_right_t3");
		
		//全国CDN资源概览
		map.put("CDN资源数据汇总","t_nation_cdn_res_left1");
		map.put("CDN资源地图数据","t_nation_cdn_res_map");
		map.put("各省CDN IP节点","t_nation_cdn_res_right1_t1");
		map.put("各省CDN企业","t_nation_cdn_res_right1_t2");
		map.put("各省CDN加速域名","t_nation_cdn_res_right1_t3");
		map.put("各企业CDN IP节点","t_nation_cdn_res_right2_t1");
		map.put("各企业CDN加速域","t_nation_cdn_res_right2_t2");
		
		//全国域名资源视图
		map.put("标题汇总-全国域名","t_nation_domain_res_count");
		map.put("省内资源排名TOP5","t_nation_domain_res_left1");
		map.put("域名机构排名TOP10","t_nation_domain_res_left2");
		map.put("资源数量","t_nation_domain_res_map");
		map.put("国内注册域名规模排名","t_nation_domain_res_right");
		
		//IPv6监测指标体系
		map.put("IPv6前8个图表数据","t_ipv6_survey");
		map.put("各省IPv4-IPv6地址用户日均解析量排名","t_ipv6_survey_right3");
		
		//全国互联网网站视图
		map.put("全国网站类型占比","t_internet_website_left1");
		map.put("企业接入网站数量占比情况","t_internet_website_left2");
		map.put("全国信安系统已覆盖的网站数", "t_internet_website_count");
		map.put("全国网站数量分布、各省网站数量排名","t_internet_website_map_right1");
		map.put("网站访问量TOP10","t_internet_website_right2");
		
		//重点地区网站视图
		map.put("网站接入占比","t_important_area_left1_t1");
		map.put("重点业务网站分类占比","t_important_area_left1_t2");
		map.put("企业接入网站数量TOP10","t_important_area_left2");
		map.put("网站分布","t_important_area_map");
		map.put("网站访问量排行","t_important_area_right1");
		map.put("重点业务网站监测","t_important_area_right2");
		
		//网站定位管理
		map.put("主体信息","t_website_location_left1");
		map.put("接入信息","t_website_location_left2");
		map.put("网站定位","t_website_location_map");
		map.put("IDC系统数据","t_website_location_right1");
		map.put("CDN系统数据","t_website_location_right2");
		map.put("云计算数据","t_website_location_right3");
		
		//网站历史追踪
		map.put("网站历史追踪展示","t_website_history_track");
		map.put("历史追踪经纬度","t_website_history_lon_lat");
		
		//移动上网日志
		map.put("日志留存分布图","t_internet_log_map");
		map.put("上网方式占比","t_internet_log_left2");
		map.put("运营商日志留存准确率趋势","t_internet_log_right1");
		map.put("上网方式日志留存准确率趋势","t_internet_log_right2");
		
		//互联网+
		map.put("全国网站数","t_internet_plus_count");
		map.put("全国网站分布","t_internet_plus_left1_map");
		map.put("金融类-重点P2P网站访问量情况","t_finance_left2");
		map.put("接入企业网站数量排名","t_internet_plus_left2");
		map.put("重点网站访问量TOP10","t_internet_plus_right1");
		map.put("网站访问量监控","t_internet_plus_right2");
		map.put("全国电商类购物网站访问量情况","t_electric_right2");
		
		//平台资源覆盖情况
		map.put("平台资源覆盖情况-数据汇总","t_platform_resource_count");
		return map.get(key);
	}
	
	public boolean valid(StringBuffer validInfo, Workbook workbook,
			int columns, Row row, String fileType) throws IOException {
		boolean validFlag = true;
		Cell addCell = null;
		// 如果存在要校验失败信息，则将校验结果追加到源文件中
		if (validInfo.length() > 0) {
			validFlag = false;
			// 生成红色字体样式
			CellStyle style1 = workbook.createCellStyle();
			Font font = workbook.createFont();
			font.setColor(HSSFColor.RED.index);
			font.setFontHeightInPoints((short) 11);
			style1.setFont(font);

			// 在当前行的colNum列上创建单元格
			addCell = row.createCell(columns);
			// 添加内容至单元格
			addCell.setCellValue(validInfo.toString());
			// 设置红色字体
			addCell.setCellStyle(style1);
		}
		// 如果存在要校验失败信息，则将校验结果追加到源文件中，给客户端下载
		if (!validFlag) {
			// 新建一输出文件流
			// String path = CommonUtils.getDataTempPath();
			String path = "D:" + File.separator + "err" + File.separator;
			File tempFile = new File(path);
			if (!tempFile.exists()) {
				tempFile.mkdirs();
			}
			String fileName = mathRandom(10) + fileType;
			path = path + fileName;
			File fullPath = new File(path);
			if (fullPath.exists()) {
				fullPath.delete();
			}
			FileOutputStream fOut = new FileOutputStream(fullPath);
			// 把相应的Excel 工作簿存盘
			workbook.write(fOut);
			fOut.flush();
			// 操作结束，关闭文件
			fOut.close();
			workbook.close();
			Map<String, Object> retMap = new HashMap<String, Object>();
			// 返回用户导入的文件名给客户端下载使用
			retMap.put("filePath", path);
			retMap.put("filename", fileName);
		}
		return validFlag;
	}
	
	/**
	 * 得到Excel表中的值
	 * @param cell Excel中的每列
	 * @return Excel中每列的值
	 */
	@SuppressWarnings("static-access")
	public String getValue(Cell cell) {
		String value = "";
		if (cell != null) {
			if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
				value = String.valueOf(cell.getBooleanCellValue());// 返回布尔类型的值
			} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
				value = String.valueOf(cell.getNumericCellValue());// 返回数值类型的值
			} else {
				value = String.valueOf(cell.getStringCellValue());// 返回字符串类型的值
			}
		}
		return value;
	}
	
	public static final String mathRandom(final long length) {
		double len = Math.pow(10D, length);
		long result = (long) (len * Math.random());

		// 补齐随机数长度
		while (len / result > 10) {
			result *= 10;
		}
		return Long.toString(result);
	}
}
